import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, Between, MoreThanOrEqual } from 'typeorm';
import { User } from '../database/entities/user.entity';
import { PaymentOrder, PaymentStatus } from '../database/entities/payment-order.entity';
import * as ExcelJS from 'exceljs';
import * as dayjs from 'dayjs';

@Injectable()
export class ReportsService {
  constructor(
    @InjectRepository(User)
    private userRepository: Repository<User>,
    @InjectRepository(PaymentOrder)
    private paymentOrderRepository: Repository<PaymentOrder>,
  ) {}

  async getUserStats(startDate: string, endDate: string) {
    // 用户增长趋势
    const userGrowthQuery = `
      SELECT 
        DATE(create_time) as date,
        COUNT(*) as count
      FROM users 
      WHERE create_time BETWEEN ? AND ?
      GROUP BY DATE(create_time)
      ORDER BY date ASC
    `;

    const userGrowth = await this.userRepository.query(userGrowthQuery, [startDate, endDate]);

    // 用户总数统计
    const totalUsers = await this.userRepository.count();
    const activeUsers = await this.userRepository.count({ where: { status: 1 } });
    const newUsersToday = await this.userRepository.count({
      where: {
        createTime: MoreThanOrEqual(dayjs().startOf('day').toDate()),
      },
    });

    // 用户活跃度统计（最近7天登录）
    const recentLoginQuery = `
      SELECT COUNT(*) as count
      FROM users 
      WHERE last_login_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    `;
    const recentActiveUsers = await this.userRepository.query(recentLoginQuery);

    // 用户地区分布（基于IP的模拟数据）
    const regionDistribution = [
      { name: '北京', value: Math.floor(totalUsers * 0.25) },
      { name: '上海', value: Math.floor(totalUsers * 0.20) },
      { name: '广州', value: Math.floor(totalUsers * 0.15) },
      { name: '深圳', value: Math.floor(totalUsers * 0.12) },
      { name: '杭州', value: Math.floor(totalUsers * 0.10) },
      { name: '其他', value: Math.floor(totalUsers * 0.18) },
    ];

    return {
      totalUsers,
      activeUsers,
      newUsersToday,
      recentActiveUsers: recentActiveUsers[0]?.count || 0,
      dates: userGrowth.map(item => item.date),
      values: userGrowth.map(item => parseInt(item.count)),
      regionDistribution,
    };
  }

  async getPaymentStats(startDate: string, endDate: string) {
    // 支付趋势统计
    const paymentTrendQuery = `
      SELECT 
        DATE(create_time) as date,
        COUNT(*) as orderCount,
        SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) as revenue,
        COUNT(CASE WHEN status = 'success' THEN 1 END) as successCount
      FROM payment_orders 
      WHERE create_time BETWEEN ? AND ?
      GROUP BY DATE(create_time)
      ORDER BY date ASC
    `;

    const paymentTrend = await this.paymentOrderRepository.query(paymentTrendQuery, [startDate, endDate]);

    // 支付方式分布
    const paymentMethodQuery = `
      SELECT 
        payment_method,
        COUNT(*) as count,
        SUM(amount) as totalAmount
      FROM payment_orders 
      WHERE status = 'success' AND create_time BETWEEN ? AND ?
      GROUP BY payment_method
    `;

    const paymentMethods = await this.paymentOrderRepository.query(paymentMethodQuery, [startDate, endDate]);

    // 总体统计
    const totalOrders = await this.paymentOrderRepository.count({
      where: { createTime: Between(new Date(startDate), new Date(endDate)) },
    });

    const successOrdersQuery = `
      SELECT 
        COUNT(*) as count,
        SUM(amount) as totalAmount
      FROM payment_orders 
      WHERE status = 'success' AND create_time BETWEEN ? AND ?
    `;

    const successStats = await this.paymentOrderRepository.query(successOrdersQuery, [startDate, endDate]);

    const todayOrdersQuery = `
      SELECT COUNT(*) as count
      FROM payment_orders 
      WHERE DATE(create_time) = CURDATE()
    `;

    const todayOrders = await this.paymentOrderRepository.query(todayOrdersQuery);

    // 时段分析（24小时）
    const hourlyAnalysisQuery = `
      SELECT 
        HOUR(create_time) as hour,
        COUNT(*) as orderCount,
        SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) as revenue
      FROM payment_orders 
      WHERE create_time BETWEEN ? AND ?
      GROUP BY HOUR(create_time)
      ORDER BY hour ASC
    `;

    const hourlyAnalysis = await this.paymentOrderRepository.query(hourlyAnalysisQuery, [startDate, endDate]);

    return {
      totalOrders,
      successOrders: successStats[0]?.count || 0,
      totalRevenue: parseFloat(successStats[0]?.totalAmount || '0'),
      todayOrders: todayOrders[0]?.count || 0,
      dates: paymentTrend.map(item => item.date),
      orderCounts: paymentTrend.map(item => parseInt(item.orderCount)),
      revenues: paymentTrend.map(item => parseFloat(item.revenue)),
      successCounts: paymentTrend.map(item => parseInt(item.successCount)),
      distribution: paymentMethods.map(item => ({
        name: this.getPaymentMethodName(item.payment_method),
        value: parseInt(item.count),
        amount: parseFloat(item.totalAmount),
      })),
      hourlyData: this.fillHourlyData(hourlyAnalysis),
    };
  }

  async getSystemStats() {
    const now = new Date();
    const yesterday = new Date(now.getTime() - 24 * 60 * 60 * 1000);

    // 今日数据
    const todayUsers = await this.userRepository.count({
      where: { createTime: MoreThanOrEqual(dayjs().startOf('day').toDate()) },
    });

    const todayOrders = await this.paymentOrderRepository.count({
      where: { createTime: MoreThanOrEqual(dayjs().startOf('day').toDate()) },
    });

    const todayRevenueQuery = `
      SELECT SUM(amount) as total
      FROM payment_orders 
      WHERE status = 'success' AND create_time >= ?
    `;

    const todayRevenue = await this.paymentOrderRepository.query(todayRevenueQuery, [
      dayjs().format('YYYY-MM-DD 00:00:00'),
    ]);

    // 昨日数据对比
    const yesterdayUsers = await this.userRepository.count({
      where: { 
        createTime: Between(
          dayjs(yesterday).startOf('day').toDate(), 
          dayjs(yesterday).endOf('day').toDate()
        ) 
      },
    });

    const yesterdayOrders = await this.paymentOrderRepository.count({
      where: { 
        createTime: Between(
          dayjs(yesterday).startOf('day').toDate(), 
          dayjs(yesterday).endOf('day').toDate()
        ) 
      },
    });

    return {
      todayUsers,
      todayOrders,
      todayRevenue: parseFloat(todayRevenue[0]?.total || '0'),
      userGrowthRate: yesterdayUsers > 0 ? ((todayUsers - yesterdayUsers) / yesterdayUsers * 100).toFixed(2) : '0',
      orderGrowthRate: yesterdayOrders > 0 ? ((todayOrders - yesterdayOrders) / yesterdayOrders * 100).toFixed(2) : '0',
    };
  }

  async exportUserReport(startDate: string, endDate: string): Promise<Buffer> {
    const users = await this.userRepository.find({
      where: { createTime: Between(new Date(startDate), new Date(endDate)) },
      order: { createTime: 'DESC' },
    });

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('用户报表');

    // 设置表头
    worksheet.columns = [
      { header: '用户ID', key: 'id', width: 10 },
      { header: '用户名', key: 'username', width: 20 },
      { header: '邮箱', key: 'email', width: 30 },
      { header: '昵称', key: 'nickname', width: 20 },
      { header: '状态', key: 'status', width: 10 },
      { header: '注册时间', key: 'createTime', width: 20 },
      { header: '最后登录', key: 'lastLoginAt', width: 20 },
    ];

    // 添加数据
    users.forEach(user => {
      worksheet.addRow({
        id: user.id,
        username: user.username,
        email: user.email,
        nickname: user.nickname || '-',
        status: user.status === 1 ? '启用' : '禁用',
        createTime: dayjs(user.createTime).format('YYYY-MM-DD HH:mm:ss'),
        lastLoginAt: user.lastLoginAt ? dayjs(user.lastLoginAt).format('YYYY-MM-DD HH:mm:ss') : '-',
      });
    });

    // 设置样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6E6FA' },
    };

    return workbook.xlsx.writeBuffer() as Promise<Buffer>;
  }

  async exportPaymentReport(startDate: string, endDate: string): Promise<Buffer> {
    const orders = await this.paymentOrderRepository.find({
      where: { createTime: Between(new Date(startDate), new Date(endDate)) },
      order: { createTime: 'DESC' },
    });

    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('支付报表');

    // 设置表头
    worksheet.columns = [
      { header: '订单号', key: 'orderNo', width: 25 },
      { header: '金额', key: 'amount', width: 15 },
      { header: '状态', key: 'status', width: 12 },
      { header: '支付方式', key: 'paymentMethod', width: 15 },
      { header: '第三方订单号', key: 'thirdPartyOrderNo', width: 25 },
      { header: '描述', key: 'description', width: 30 },
      { header: '创建时间', key: 'createTime', width: 20 },
      { header: '完成时间', key: 'completeTime', width: 20 },
    ];

    // 添加数据
    orders.forEach(order => {
      worksheet.addRow({
        orderNo: order.orderNo,
        amount: order.amount,
        status: this.getStatusText(order.status),
        paymentMethod: this.getPaymentMethodName(order.paymentMethod),
        thirdPartyOrderNo: order.thirdPartyOrderNo || '-',
        description: order.description || '-',
        createTime: dayjs(order.createTime).format('YYYY-MM-DD HH:mm:ss'),
        completeTime: order.completeTime ? dayjs(order.completeTime).format('YYYY-MM-DD HH:mm:ss') : '-',
      });
    });

    // 设置样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6E6FA' },
    };

    return workbook.xlsx.writeBuffer() as Promise<Buffer>;
  }

  private getPaymentMethodName(method: string): string {
    const methodMap = {
      alipay: '支付宝',
      wechat: '微信支付',
      bank: '银行卡',
      balance: '余额支付',
    };
    return methodMap[method] || method;
  }

  private getStatusText(status: PaymentStatus): string {
    const statusMap = {
      pending: '待支付',
      success: '支付成功',
      failed: '支付失败',
      cancelled: '已取消',
    };
    return statusMap[status] || status;
  }

  private fillHourlyData(hourlyAnalysis: any[]): any[] {
    const hourlyData = Array.from({ length: 24 }, (_, hour) => ({
      hour,
      orderCount: 0,
      revenue: 0,
    }));

    hourlyAnalysis.forEach(item => {
      const hour = parseInt(item.hour);
      hourlyData[hour] = {
        hour,
        orderCount: parseInt(item.orderCount),
        revenue: parseFloat(item.revenue),
      };
    });

    return hourlyData;
  }
}